package edu.sdjzu.exam.mapper.exam;

import edu.sdjzu.exam.domain.exam.ExamAnswer;
import org.apache.ibatis.annotations.*;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

@Mapper
public interface ExamAnswersMapper {

    @Insert("INSERT INTO exam_answers (session_id, exam_id, student_id, question_id, answer_content, file_path, is_draft, answer_time) " +
            "VALUES (#{sessionId}, #{examId}, #{studentId}, #{questionId}, #{answerContent}, #{filePath}, #{isDraft}, #{answerTime})")
    @Options(useGeneratedKeys = true, keyProperty = "answerId")
    void insertExamAnswer(ExamAnswer examAnswer);

    @Select("SELECT * FROM exam_answers WHERE answer_id = #{answerId}")
    ExamAnswer selectAnswerById(@Param("answerId") Long answerId);

    @Select("SELECT * FROM exam_answers WHERE exam_id = #{examId} AND student_id = #{studentId} AND is_draft = false")
    List<ExamAnswer> selectAnswersByExamAndStudent(@Param("examId") Long examId, @Param("studentId") Long studentId);

    @Select("SELECT * FROM exam_answers WHERE exam_id = #{examId} AND student_id = #{studentId} AND question_id = #{questionId} AND session_id = #{sessionId}")
    ExamAnswer selectAnswerByExamStudentAndQuestion(@Param("examId") Long examId, 
                                                   @Param("studentId") Long studentId, 
                                                   @Param("questionId") Long questionId,
                                                   @Param("sessionId") Long sessionId);

    @Update("UPDATE exam_answers SET answer_content = #{answerContent}, file_path = #{filePath}, " +
            "is_draft = #{isDraft}, answer_time = #{answerTime}, updated_at = NOW() " +
            "WHERE answer_id = #{answerId}")
    void updateExamAnswer(ExamAnswer examAnswer);

    @Update("UPDATE exam_answers SET score = #{score}, marked_by = #{markedBy}, marked_at = #{markedAt}, " +
            "comment = #{comment}, updated_at = NOW() " +
            "WHERE answer_id = #{answerId}")
    void updateAnswerScore(@Param("answerId") Long answerId,
                          @Param("score") BigDecimal score,
                          @Param("markedBy") Long markedBy,
                          @Param("markedAt") LocalDateTime markedAt,
                          @Param("comment") String comment);

    @Select("SELECT COUNT(*) FROM exam_answers WHERE exam_id = #{examId} AND student_id = #{studentId}")
    int countAnswersByExamAndStudent(@Param("examId") Long examId, @Param("studentId") Long studentId);

    @Select("SELECT COUNT(*) FROM exam_answers WHERE exam_id = #{examId} AND student_id = #{studentId} AND is_draft = 0")
    int countSubmittedAnswersByExamAndStudent(@Param("examId") Long examId, @Param("studentId") Long studentId);

    @Select("SELECT ea.answer_id, ea.question_id, ea.answer_content, ea.file_path, ea.score, ea.comment, " +
            "ea.marked_by, q.question_type, q.content as question_content, q.reference_answer, eq.score as points, " +
            "u.realname as marked_by_name " +
            "FROM exam_answers ea " +
            "JOIN questions q ON ea.question_id = q.question_id " +
            "JOIN exam_questions eq ON ea.exam_id = eq.exam_id AND ea.question_id = eq.question_id " +
            "LEFT JOIN users u ON ea.marked_by = u.user_id " +
            "WHERE ea.exam_id = #{examId} AND ea.student_id = #{studentId} AND ea.is_draft = false " +
            "AND q.question_type IN ('subjective', 'programming', 'file_upload') " +
            "ORDER BY eq.question_order")
    List<Map<String, Object>> selectSubjectiveAnswersByExamAndStudent(@Param("examId") Long examId, @Param("studentId") Long studentId);

    @Select("SELECT COUNT(*) FROM exam_answers ea " +
            "JOIN questions q ON ea.question_id = q.question_id " +
            "WHERE ea.exam_id = #{examId} AND ea.student_id = #{studentId} AND ea.is_draft = false " +
            "AND q.question_type IN ('subjective', 'programming', 'file_upload') " +
            "AND ea.score IS NULL")
    int countUnGradedSubjectiveAnswers(@Param("examId") Long examId, @Param("studentId") Long studentId);

    @Select("SELECT COUNT(*) FROM exam_answers ea " +
            "JOIN questions q ON ea.question_id = q.question_id " +
            "WHERE ea.exam_id = #{examId} AND ea.student_id = #{studentId} AND ea.is_draft = false " +
            "AND q.question_type IN ('subjective', 'programming', 'file_upload')")
    int countTotalSubjectiveAnswers(@Param("examId") Long examId, @Param("studentId") Long studentId);

    @Select("SELECT COUNT(*) FROM exam_answers ea " +
            "JOIN questions q ON ea.question_id = q.question_id " +
            "WHERE ea.exam_id = #{examId} AND ea.student_id = #{studentId} AND ea.is_draft = false " +
            "AND q.question_type IN ('subjective', 'programming', 'file_upload') " +
            "AND ea.score IS NOT NULL")
    int countGradedSubjectiveAnswers(@Param("examId") Long examId, @Param("studentId") Long studentId);

    @Select("SELECT COALESCE(SUM(ea.score), 0) as total_score " +
            "FROM exam_answers ea " +
            "WHERE ea.exam_id = #{examId} AND ea.student_id = #{studentId} AND ea.is_draft = false AND ea.score IS NOT NULL")
    BigDecimal sumScoresByExamAndStudent(@Param("examId") Long examId, @Param("studentId") Long studentId);

    @Select("SELECT COALESCE(SUM(eq.score), 0) as max_score " +
            "FROM exam_questions eq " +
            "WHERE eq.exam_id = #{examId}")
    BigDecimal getMaxScoreByExam(@Param("examId") Long examId);

    // 新增基于sessionId的方法
    
    @Select("SELECT es.exam_id, es.student_id FROM exam_sessions es WHERE es.session_id = #{sessionId}")
    Map<String, Object> getExamAndStudentBySessionId(@Param("sessionId") Long sessionId);

    @Select("SELECT ea.*, q.question_type, q.content as question_content, q.reference_answer, eq.score as points, " +
            "u.realname as marked_by_name " +
            "FROM exam_answers ea " +
            "JOIN questions q ON ea.question_id = q.question_id " +
            "JOIN exam_questions eq ON ea.exam_id = eq.exam_id AND ea.question_id = eq.question_id " +
            "LEFT JOIN users u ON ea.marked_by = u.user_id " +
            "WHERE ea.session_id = #{sessionId} " +
            "AND q.question_type IN ('subjective', 'programming', 'file_upload') " +
            "ORDER BY eq.question_order")
    List<Map<String, Object>> selectSubjectiveAnswersBySession(@Param("sessionId") Long sessionId);

    @Select("SELECT COUNT(*) FROM exam_answers ea " +
            "JOIN questions q ON ea.question_id = q.question_id " +
            "WHERE ea.session_id = #{sessionId} " +
            "AND q.question_type IN ('subjective', 'programming', 'file_upload') " +
            "AND ea.score IS NULL")
    int countUnGradedSubjectiveAnswersBySession(@Param("sessionId") Long sessionId);

    @Select("SELECT COUNT(*) FROM exam_answers WHERE session_id = #{sessionId}")
    int countAnswersBySession(@Param("sessionId") Long sessionId);

    @Select("SELECT COALESCE(SUM(ea.score), 0) as total_score " +
            "FROM exam_answers ea " +
            "WHERE ea.session_id = #{sessionId} AND ea.score IS NOT NULL")
    BigDecimal sumScoresBySession(@Param("sessionId") Long sessionId);
}